package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.icim.entity.Spc_ret_mfg_data;

@Repository
public class SpcRetMfgDataDao {
    @Resource
    private JdbcTemplate icimJdbcTemplate;

    private Spc_ret_mfg_data setEntity(ResultSet rs) throws SQLException {
        Spc_ret_mfg_data spc_ret_mfg_data = new Spc_ret_mfg_data();
        spc_ret_mfg_data.setPrd_seq_id_fk(rs.getString("PRD_SEQ_ID_FK"));
        spc_ret_mfg_data.setOpe_id_fk(rs.getString("OPE_ID_FK"));
        spc_ret_mfg_data.setTool_id_fk(rs.getString("TOOL_ID_FK"));
        spc_ret_mfg_data.setMes_id_fk(rs.getString("MES_ID_FK"));
        spc_ret_mfg_data.setAc_recipe_id(rs.getString("AC_RECIPE_ID"));
        spc_ret_mfg_data.setRpt_timestamp(rs.getTimestamp("RPT_TIMESTAMP"));
        spc_ret_mfg_data.setData_cnt(rs.getInt("DATA_CNT"));
        spc_ret_mfg_data.setRpt_usr(rs.getString("RPT_USR"));
        spc_ret_mfg_data.setData_pat_fk(rs.getString("DATA_PAT_FK"));

        spc_ret_mfg_data.setData_value_001(rs.getString("DATA_VALUE_001"));
        spc_ret_mfg_data.setData_value_002(rs.getString("DATA_VALUE_002"));
        spc_ret_mfg_data.setData_value_003(rs.getString("DATA_VALUE_003"));
        spc_ret_mfg_data.setData_value_004(rs.getString("DATA_VALUE_004"));
        spc_ret_mfg_data.setData_value_005(rs.getString("DATA_VALUE_005"));
        spc_ret_mfg_data.setData_value_006(rs.getString("DATA_VALUE_006"));
        spc_ret_mfg_data.setData_value_007(rs.getString("DATA_VALUE_007"));
        spc_ret_mfg_data.setData_value_008(rs.getString("DATA_VALUE_008"));
        spc_ret_mfg_data.setData_value_009(rs.getString("DATA_VALUE_009"));
        spc_ret_mfg_data.setData_value_010(rs.getString("DATA_VALUE_010"));
        spc_ret_mfg_data.setData_value_011(rs.getString("DATA_VALUE_011"));
        spc_ret_mfg_data.setData_value_012(rs.getString("DATA_VALUE_012"));
        spc_ret_mfg_data.setData_value_013(rs.getString("DATA_VALUE_013"));
        spc_ret_mfg_data.setData_value_014(rs.getString("DATA_VALUE_014"));
        spc_ret_mfg_data.setData_value_015(rs.getString("DATA_VALUE_015"));
        spc_ret_mfg_data.setData_value_016(rs.getString("DATA_VALUE_016"));
        spc_ret_mfg_data.setData_value_017(rs.getString("DATA_VALUE_017"));
        spc_ret_mfg_data.setData_value_018(rs.getString("DATA_VALUE_018"));
        spc_ret_mfg_data.setData_value_019(rs.getString("DATA_VALUE_019"));
        spc_ret_mfg_data.setData_value_020(rs.getString("DATA_VALUE_020"));
        spc_ret_mfg_data.setData_value_021(rs.getString("DATA_VALUE_021"));
        spc_ret_mfg_data.setData_value_022(rs.getString("DATA_VALUE_022"));
        spc_ret_mfg_data.setData_value_023(rs.getString("DATA_VALUE_023"));
        spc_ret_mfg_data.setData_value_024(rs.getString("DATA_VALUE_024"));
        spc_ret_mfg_data.setData_value_025(rs.getString("DATA_VALUE_025"));
        spc_ret_mfg_data.setData_value_026(rs.getString("DATA_VALUE_026"));
        spc_ret_mfg_data.setData_value_027(rs.getString("DATA_VALUE_027"));
        spc_ret_mfg_data.setData_value_028(rs.getString("DATA_VALUE_028"));
        spc_ret_mfg_data.setData_value_029(rs.getString("DATA_VALUE_029"));
        spc_ret_mfg_data.setData_value_030(rs.getString("DATA_VALUE_030"));
        return spc_ret_mfg_data;
    }

    public List<Spc_ret_mfg_data> getCsotSpcDataRecorders(Timestamp beginTimeStamp, Timestamp endTimeStamp,
            String[] opeIds, String mesId1, String mesId2, String cusId) {
        StringBuffer sqlBuffer = new StringBuffer("SELECT * FROM SPC_RET_MFG_DATA WHERE");
        sqlBuffer.append(" RPT_TIMESTAMP BETWEEN ? AND ? ");
        sqlBuffer.append(" AND OPE_ID_FK IN ( ");
        for (int i = 0; i < opeIds.length; i++) {
            String opeId = opeIds[i];
            sqlBuffer.append("'").append(opeId).append("'");
            if (i < opeIds.length - 1) {
                sqlBuffer.append(",");
            }
        }
        sqlBuffer.append(" )");
        sqlBuffer.append(" AND MES_ID_FK IN (?,?) ");
        sqlBuffer.append(" AND PRD_SEQ_ID_FK IN (");
        sqlBuffer.append(" SELECT PRD_SEQ_ID FROM RET_PRD_INFO WHERE WO_ID_FK IN ( ");
        sqlBuffer.append(" SELECT WO_ID FROM RET_WO WHERE WO_TYP = 'W' AND CUS_ID_FK = ? ))");
        return icimJdbcTemplate.query(sqlBuffer.toString(), new Object[] { beginTimeStamp, endTimeStamp, mesId1,
                mesId2, cusId }, new RowMapper<Spc_ret_mfg_data>() {

            @Override
            public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                return setEntity(rs);
            }
        });

    }

    public Spc_ret_mfg_data getCsotSpcDataRecorderByShtId(String sheetId) {
        String sql = "SELECT * FROM SPC_RET_MFG_DATA WHERE PRD_SEQ_ID_FK=?";
        List<Spc_ret_mfg_data> spc_ret_mfg_datas = (List<Spc_ret_mfg_data>) icimJdbcTemplate.query(sql,
                new Object[] { sheetId }, new RowMapper<Spc_ret_mfg_data>() {

                    @Override
                    public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return setEntity(rs);
                    }

                });

        return spc_ret_mfg_datas.size() > 0 ? spc_ret_mfg_datas.get(0) : new Spc_ret_mfg_data();
    }

    public List<Spc_ret_mfg_data> getCsotSpcDataRecorderByShtIds(Collection<String> sheetIds) {
        String sql = "SELECT * FROM SPC_RET_MFG_DATA WHERE PRD_SEQ_ID_FK in (:sheetIds)";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("sheetIds", sheetIds);

        
        NamedParameterJdbcTemplate icimNameJdbcTemplate = new NamedParameterJdbcTemplate(icimJdbcTemplate);
        
        return icimNameJdbcTemplate.query(sql, parameters, new RowMapper<Spc_ret_mfg_data>() {

            @Override
            public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                return setEntity(rs);
            }
        });

    }
    public List<Spc_ret_mfg_data> getCsotSpcDataRecorderByShtIdsMesIds(Collection<String> sheetIds,Collection<String> mesIds) {
        String sql = "SELECT * FROM SPC_RET_MFG_DATA WHERE PRD_SEQ_ID_FK in (:sheetIds) and MES_ID_FK IN (:mesIds)";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("sheetIds", sheetIds);
        parameters.put("mesIds", mesIds);
        
        NamedParameterJdbcTemplate icimNameJdbcTemplate = new NamedParameterJdbcTemplate(icimJdbcTemplate);
        
        return icimNameJdbcTemplate.query(sql, parameters, new RowMapper<Spc_ret_mfg_data>() {
            
            @Override
            public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                return setEntity(rs);
            }
        });
        
    }

    public List<Spc_ret_mfg_data> getCsotSpcDataRecordersByTime(Timestamp beginTimestamp, Timestamp endTimestamp,String rptUnit) {
        String sql = "SELECT * FROM SPC_RET_MFG_DATA  WHERE RPT_TIMESTAMP BETWEEN ? AND ? AND REP_UNIT_FK = ?  AND MES_ID_FK NOT IN ('CDPC','CZPC') ";
        return (List<Spc_ret_mfg_data>) icimJdbcTemplate.query(sql, new Object[] { beginTimestamp, endTimestamp,"P" },
                new RowMapper<Spc_ret_mfg_data>() {

                    @Override
                    public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return setEntity(rs);
                    }
                });
    }
    
    
    public List<Spc_ret_mfg_data> getCsotSpcDataRecordersForRet005(String prd_seq_id_fk) {
      //SELECT * FROM spc_ret_mfg_data t WHERE t.ope_id_fk = 'JB-08' AND t.mes_id_fk = 'TMPG' AND t.prd_seq_id_fk = ''
      String sql = "SELECT * FROM spc_ret_mfg_data t WHERE t.ope_id_fk = 'JB-08' AND t.mes_id_fk = 'TMPG' AND t.prd_seq_id_fk = ? ORDER BY t.rpt_timestamp DESC";
      return (List<Spc_ret_mfg_data>) icimJdbcTemplate.query(sql, new Object[] { prd_seq_id_fk},
              new RowMapper<Spc_ret_mfg_data>() {

                  @Override
                  public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                      return setEntity(rs);
                  }
              });
  }
    
    public List<Spc_ret_mfg_data> getTwqcSpcDataRecorders(String prd_seq_id_fk) {
        String sql = "SELECT * FROM spc_ret_mfg_data t WHERE t.ope_id_fk not in('JB-01','JB-02','JB-03','JB-04','JB-05','JB-06','JB-07','JB-08') and t.mes_id_fk = 'CDPC' AND t.prd_seq_id_fk = ? ORDER BY t.rpt_timestamp DESC";
        return (List<Spc_ret_mfg_data>) icimJdbcTemplate.query(sql, new Object[] { prd_seq_id_fk},
                new RowMapper<Spc_ret_mfg_data>() {

                    @Override
                    public Spc_ret_mfg_data mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return setEntity(rs);
                    }
                });
    }

}
